import json
import frappe
from frappe import _
from pypika.terms import Tuple
from frappe.utils import ceil, flt,now_datetime
from frappe.query_builder.functions import IfNull, Sum
from pypika.terms import ExistsCriterion
from erpnext.manufacturing.doctype.production_plan.production_plan import (
ProductionPlan,get_sub_assembly_items, get_item_details)
from erpnext.manufacturing.doctype.work_order.work_order import WorkOrder
from zelin_pp.utils import (get_item_code_map,get_projected_qty_map,get_qty,
    get_whole_number_uom_list,get_item_warehouse_qty_map,reverse_reserved_qty)


class CustomProductionPlan(ProductionPlan):
    def add_items(self, items):
        #先剔除原材料 防止出错提示 默认BOM 01010100103未找到  #不添加非库存物料和已禁用物料          
        item = frappe.qb.DocType('Item')
        item_default = frappe.qb.DocType('Item Default')
        item_details = frappe.qb.from_(item
            ).left_join(item_default
            ).on(item.name == item_default.parent
            ).select(item.name, item.default_material_request_type, item.is_sub_contracted_item,
                item.stock_uom,item_default.default_supplier
            ).where( (item.name.isin({row.item_code for row in items})) &
                    (item.is_stock_item == 1) & (item.disabled == 0) &
                    ((item_default.company == self.company) | (item_default.company.isnull()))
            ).run(as_dict = 1)
        item_map = {d.name:d for d in item_details}
        #仅将默认物料需求类型为工单入库或勾选委外的成品物料添加到装配件(成品明细表)
        fg_item_codes = {d.name for d in item_details if d.default_material_request_type == 'Manufacture' or d.is_sub_contracted_item}
        fg_items = [d for d in items if d.item_code in fg_item_codes]
        rm_items = [d for d in items if d.item_code not in fg_item_codes and d.item_code in item_map]          
        for rm_item in rm_items:
            rm_item.stock_uom = item_map.get(rm_item.item_code,{}).get('stock_uom')                
        super(CustomProductionPlan,self).add_items(fg_items)
        self.add_rm_items(rm_items)

        #生产类型处理              
        for item in self.po_items:            
            item_info = item_map.get(item.item_code)                       
            if item_info.is_sub_contracted_item:
                item.type_of_manufacturing = 'Subcontract'
                item.supplier = item_info.default_supplier or ''
            elif item_info.default_material_request_type == 'Manufacture':
                item.type_of_manufacturing = 'In House'
            else:
                item.type_of_manufacturing = 'Material Request'
        if self.po_items:                    
            self.process_fg_projected_qty()
            #删除考虑成品可用库存后计划数量为0的行
            if self.skip_available_assembly_item:
                self.po_items = [item for item in self.po_items if item.get('planned_qty')]                   

    def add_rm_items(self, rm_items):
        for data in rm_items:
            #不添加直运物料，Pending数量为0
            if not (data.planned_qty or data.pending_qty):continue
            pi = self.append(
                "po_items",
                {
                    "warehouse": data.warehouse,
                    "item_code": data.item_code,
                    "description": data.description,
                    "stock_uom": data.stock_uom or "",
                    "pending_qty": data.pending_qty
                }
            )
            pi._set_defaults()
            if self.get_items_from == "Sales Order":
                pi.sales_order = data.parent
                pi.sales_order_item = data.name                
            elif self.get_items_from == "Material Request":
                pi.material_request = data.parent
                pi.material_request_item = data.name

    def process_fg_projected_qty(self):        
        item_warehouse_tuple = [(d.item_code,d.warehouse) for d in self.po_items]        
        qty_map = get_item_warehouse_qty_map(item_warehouse_tuple)
        reverse_reserved_qty(self.po_items, qty_map)

        for item in self.po_items:
            current_qty_map = qty_map.get((item.item_code, item.warehouse),{})
            item.projected_qty = current_qty_map.get('projected_qty',0)        
            item.actual_qty = current_qty_map.get('actual_qty',0)        
            if not item.planned_qty and item.pending_qty:
                item.planned_qty = item.pending_qty
            #推迟到原材料需求部分处理    
            if item.get('type_of_manufacturing') != "Material Request":    
                if self.skip_available_assembly_item and item.projected_qty > 0:
                    plan_reserved_stock_qty = item.planned_qty if item.projected_qty >= item.planned_qty else item.projected_qty
                    item.planned_qty -= plan_reserved_stock_qty
                    item.plan_reserved_stock_qty = plan_reserved_stock_qty
        if self.skip_available_assembly_item and not self.combine_items:    
            self.process_non_combined_items_qty()
        self.update_ref_items_plan_reserved_stock_qty()

    def process_non_combined_items_qty(self):                    
        """处理同一个物料在多个销售订单、物料需求中的场景    
           数据结构{item:{count:1, total_pending_qty}}
           计算逻辑：按物料计算可用库存，按订单依次分派库存，直到用完
        """
        item_map, qty_dict = {}, {}
        for item in self.po_items:
            #推迟到原材料需求部分处理    
            if item.get('type_of_manufacturing') != "Material Request":              
                key = (item.item_code, item.warehouse)
                qty_dict = item_map.setdefault(key, {'count':0,'total_pending_qty':0, 'rows':[]})
                qty_dict['count'] += 1
                qty_dict['total_pending_qty'] += item.pending_qty
                qty_dict['projected_qty'] = item.projected_qty
                qty_dict['rows'].append(item)
        #过滤出有同类项(多个订单)的物料
        combined_items = {k:item_map[k] for k in item_map if item_map[k]['count']>1}
        for (key, values) in combined_items.items():
            projected_qty = values['projected_qty']
            running_atp = projected_qty if projected_qty and projected_qty > 0 else 0            
            for row in values['rows']:
                row.running_atp = running_atp
                atp = 0                            
                if running_atp > 0:
                    atp = row.pending_qty if running_atp > row.pending_qty else running_atp
                running_atp -= atp
                row.plan_reserved_stock_qty = atp
                row.planned_qty = row.pending_qty - atp 

    def update_ref_items_plan_reserved_stock_qty(self):
        if self.skip_available_assembly_item and self.combine_items:
            #reference_item是用合并后保留的最后一个sales_order_item关联的
            plan_item_qty_map = {
                item.get('sales_order_item'):{'reserved_qty': item.get('plan_reserved_stock_qty')} for item in self.po_items
                if item.get('bom_no') or item.get('bom')
            }
            for row in self.prod_plan_references:
                row.qty = flt(row.qty)  #qty被定义为了数据类型                
                qty_map = plan_item_qty_map.get(row.item_reference,{})
                running_atp = qty_map.get('reserved_qty', 0)
                atp = 0                            
                if running_atp and running_atp > 0:
                    atp = row.qty if running_atp > row.qty else running_atp
                    running_atp -= atp
                    row.plan_reserved_stock_qty = atp
                    qty_map['reserved_qty'] -= atp 

    def get_production_items(self):
        """为不影响正常成品工单创建，只保留成品物料，剔除add_items中添加的委外及原材料，另外处理"""
        item_dict = super(CustomProductionPlan,self).get_production_items()
        fg_items = set(frappe.get_all('Item', pluck='name', order_by=None,            
            filters= {'name':('in', {row.item_code for row in self.po_items}),
            'default_material_request_type':'Manufacture'}            
        ))
        #仅保留成品料号
        item_dict = {key:item_dict[key] for key in item_dict if key[0] in fg_items}
        return item_dict

    @frappe.whitelist()
    def make_work_order(self):
        """添加装配件子表中委外明细到委外订单清单中"""

        super(CustomProductionPlan,self).make_work_order()
        subcontracted_po = {}
        po_list = []
        for row in self.po_items:
            if row.type_of_manufacturing == "Subcontract":
                row.schedule_date = row.planned_start_date  #装配件子表无计划日，直接取计划开始日
                row.production_item = row.item_code
                row.fg_warehouse = row.warehouse
                row.qty=row.planned_qty                
                #row.name 是plan_item会被赋值到production_plan_sub_assembly_item数据字段，无其它业务逻辑，
                #无实质影响，这里就不处理了
                subcontracted_po.setdefault(row.supplier, []).append(row)
        self.make_subcontracted_purchase_order(subcontracted_po, po_list)
        self.show_list_created_message("Purchase Order", po_list)

    def make_work_order_for_subassembly_items(self, wo_list, subcontracted_po, default_warehouses):
        self.sub_assembly_items = [row for row in self.sub_assembly_items 
            if row.get('type_of_manufacturing') != 'Phantom']
        super(CustomProductionPlan,self).make_work_order_for_subassembly_items(wo_list, subcontracted_po, default_warehouses)

    @frappe.whitelist()
    def get_sub_assembly_items(self, manufacturing_type=None):
        """ 
        本功能实现如果勾选 skip_available_sub_assembly_item 子装配件考虑当前仓库预期可用数量
        仓库逻辑修改为优先取物料主数据中子装配件默认仓库，标准功能继承装配件的（成品)仓码
        取物料主数据中定义的默认供应商
        合并多行子装配件需求数量，以便最后进行最小订单与最小包装量处理
        按料号排序
        预期可用数量按计划行顺序依次扣减
        计划数量按最小订单与最小包装量进行调整
        单位是仅限整数则圆整
        """
        #super(CustomProductionPlan,self).get_sub_assembly_items(manufacturing_type = manufacturing_type)
        bom_map.update({})                  #初始化模块变量        
        get_sub_assembly_bom_map(self.company)
        qty_map = get_sub_assembly_qty_map(self.company)
        self.sub_assembly_items = []
        sub_assembly_items_store = []  # temporary store to process all subassembly items

        for row in self.po_items:
            if not row.item_code:
                frappe.throw(_("Row #{0}: Please select Item Code in Assembly Items").format(row.idx))

            bom_data = []

            warehouse = row.warehouse if self.get('skip_available_sub_assembly_item') else None
            custom_get_sub_assembly_items(row.bom_no, bom_data, row.planned_qty, self.company, warehouse=warehouse, qty_map=qty_map)
            self.set_sub_assembly_items_based_on_level(row, bom_data, manufacturing_type)
            sub_assembly_items_store.extend(bom_data)

        if self.combine_sub_items:
            # Combine subassembly items
            sub_assembly_items_store = self.combine_subassembly_items(sub_assembly_items_store)

        sub_assembly_items_store.sort(key=lambda d: d.bom_level, reverse=True)  # sort by bom level

        for idx, row in enumerate(sub_assembly_items_store):
            row.idx = idx + 1
            self.append("sub_assembly_items", row)

        self.set_default_supplier_for_subcontracting_order()                
        if not self.sub_assembly_items: return                
        #按物料号排序
        self.sub_assembly_items = sorted(self.sub_assembly_items, key=lambda row: row.production_item )
        for i, row in enumerate(self.sub_assembly_items):
            row.idx = i + 1

        item_code_list = [item.production_item for item in self.sub_assembly_items]
        item_code_map = get_item_code_map(item_code_list, self.company)
        uom_list = tuple({item.uom for item in self.sub_assembly_items})
        whole_number_uom_list = get_whole_number_uom_list(uom_list)

        for row in self.sub_assembly_items:
            item_code = row.production_item
            min_order_qty, min_pack_qty, warehouse, _ = item_code_map.get(item_code, (0,0,'', ''))
            row.qty = get_qty(row.qty or 0, min_order_qty, min_pack_qty)
            if row.uom in whole_number_uom_list:
                row.qty = ceil(row.qty)

        #修复预期可用库存未写回的bug        
        item_warehouse_tuple = [(d.production_item,d.fg_warehouse) for d in self.sub_assembly_items]        
        qty_map = get_item_warehouse_qty_map(item_warehouse_tuple)
        for item in self.sub_assembly_items:
            item.projected_qty = qty_map.get((item.production_item, item.fg_warehouse),{}).get('projected_qty',0) 
            item.actual_qty = qty_map.get((item.production_item, item.fg_warehouse),{}).get('actual_qty',0) 

    def set_sub_assembly_items_based_on_level(self, row, bom_data, manufacturing_type=None):
        "Modify bom_data, set additional details."
        for data in bom_data:
            data.qty = data.stock_qty
            data.production_plan_item = row.name
            #fisher 原来是row.warehouse，即固定取成品仓库，改为优先取半成品物料主数据的默认仓库
            data.fg_warehouse = data.warehouse   
            data.schedule_date = row.planned_start_date        
            if data.is_sub_contracted_item:
                manufacturing_type = "Subcontract"
            elif data.default_material_request_type == 'Manufacture':
                manufacturing_type = "In House"
            elif data.default_material_request_type == 'Phantom':
                manufacturing_type = "Phantom"
            else:
                    manufacturing_type = "Material Request"
            data.type_of_manufacturing = manufacturing_type

    def combine_subassembly_items(self, sub_assembly_items_store):
        "Aggregate if same: Item, Warehouse, Inhouse/Outhouse Manu.g, BOM No."
        key_wise_data = {}
        for row in sub_assembly_items_store:
            key = (
                row.get("production_item"),
                row.get("fg_warehouse"),
                row.get("bom_no"),
                row.get("type_of_manufacturing"),
            )
            if key not in key_wise_data:
                # intialise (item, wh, bom no, man.g type) wise dict
                key_wise_data[key] = row
                continue

            existing_row = key_wise_data[key]
            if existing_row:
                # if row with same (item, wh, bom no, man.g type) key, merge
                existing_row.required_qty += flt(row.required_qty)  #fisher customi field
                existing_row.qty += flt(row.qty)
                existing_row.stock_qty += flt(row.stock_qty)
                existing_row.bom_level = max(existing_row.bom_level, row.bom_level)
                continue
            else:
                # add row with key
                key_wise_data[key] = row

        sub_assembly_items_store = [
            key_wise_data[key] for key in key_wise_data
        ]  # unpack into single level list
        return sub_assembly_items_store

    def make_subcontracted_purchase_order(self, subcontracted_po, purchase_orders):
        """删除已生成委外采购订单的行"""
        if not subcontracted_po: return
        names = {r.name for r in self.sub_assembly_items if r.type_of_manufacturing == "Subcontract"}
        names = frappe.get_all('Purchase Order',
                                fields = ['`tabPurchase Order Item`.production_plan_sub_assembly_item'],
                                filters={'docstatus': ("<", 2),
                                         "company": self.company,
                                         "production_plan_sub_assembly_item": ("in", names)
                                        },
                                pluck = 'production_plan_sub_assembly_item')
        new_subcontracted_po = {}
        for supplier, po_list in subcontracted_po.items():
            po_list = [r for r in po_list if r.name not in names]
            if po_list: new_subcontracted_po[supplier] = po_list
        return super(CustomProductionPlan, self).make_subcontracted_purchase_order(new_subcontracted_po, purchase_orders)

    def create_work_order(self, item):
        """如果之前已创建工单则不用创建"""
        if item.get('qty'):
            if item.get("production_plan_item"):
                fieldname = "production_plan_item"
                fieldvalue = item.get("production_plan_item")
            else:
                fieldname = "production_plan_sub_assembly_item"
                fieldvalue = item.get("production_plan_sub_assembly_item")

            if not frappe.db.exists("Work Order", 
                {"company": self.company,
                fieldname: fieldvalue,
                "docstatus": ("<", 2)}):
                return super(CustomProductionPlan, self).create_work_order(item)

    def on_submit(self):
        super(CustomProductionPlan, self).on_submit()
        self.update_plan_reserved_stock_qty()
    
    def on_cancel(self):
        super(CustomProductionPlan, self).on_cancel()
        self.update_plan_reserved_stock_qty()

    def update_plan_reserved_stock_qty(self):
        names=[]
        for item in self.po_items:
            if (item.sales_order_item and item.plan_reserved_stock_qty and not item.planned_qty):
                if self.docstatus == 1:
                    frappe.db.set_value('Sales Order Item', item.sales_order_item, 
                        'plan_reserved_stock_qty', item.plan_reserved_stock_qty)
                else:
                    names.append(item.sales_order_item)
        if self.docstatus == 2 and names:
            frappe.db.set_value('Sales Order Item', {'name':('in', names)},'plan_reserved_stock_qty', 0)
    
    def get_so_items(self):
        """除原work_order_qty之外，再考虑plan_reserved_stock_qty"""
        # Check for empty table or empty rows
        if not self.get("sales_orders") or not self.get_so_mr_list("sales_order", "sales_orders"):
            frappe.throw(_("Please fill the Sales Orders table"), title=_("Sales Orders Required"))

        so_list = self.get_so_mr_list("sales_order", "sales_orders")

        bom = frappe.qb.DocType("BOM")
        so_item = frappe.qb.DocType("Sales Order Item")

        items_subquery = frappe.qb.from_(bom).select(bom.name).where(bom.is_active == 1)
        items_query = (
            frappe.qb.from_(so_item)
            .select(
                so_item.parent,
                so_item.item_code,
                so_item.warehouse,
                (
                    (so_item.qty - so_item.delivered_qty) * so_item.conversion_factor - so_item.work_order_qty  - so_item.plan_reserved_stock_qty
                ).as_("pending_qty"),
                so_item.description,
                so_item.name,
            )
            .distinct()
            .where(
                (so_item.parent.isin(so_list))
                & (so_item.docstatus == 1)
                & ((so_item.stock_qty  * so_item.conversion_factor) > so_item.delivered_qty)
                & ((so_item.qty  * so_item.conversion_factor) > (so_item.work_order_qty + so_item.plan_reserved_stock_qty) )
            )
        )

        if self.item_code and frappe.db.exists("Item", self.item_code):
            items_query = items_query.where(so_item.item_code == self.item_code)
            items_subquery = items_subquery.where(
                self.get_bom_item_condition() or bom.item == so_item.item_code
            )

        items_query = items_query.where(ExistsCriterion(items_subquery))

        items = items_query.run(as_dict=True)

        pi = frappe.qb.DocType("Packed Item")

        packed_items_query = (
            frappe.qb.from_(so_item)
            .from_(pi)
            .select(
                pi.parent,
                pi.item_code,
                pi.warehouse.as_("warehouse"),
                (((so_item.qty - (so_item.work_order_qty + so_item.plan_reserved_stock_qty)/so_item.conversion_factor) * pi.qty) / so_item.qty).as_("pending_qty"),
                pi.parent_item,
                pi.description,
                so_item.name,
            )
            .distinct()
            .where(
                (so_item.parent == pi.parent)
                & (so_item.docstatus == 1)
                & (pi.parent_item == so_item.item_code)
                & (so_item.parent.isin(so_list))
                & ((so_item.stock_qty  * so_item.conversion_factor) > so_item.delivered_qty)
                & ((so_item.qty  * so_item.conversion_factor) > (so_item.work_order_qty + - so_item.plan_reserved_stock_qty) )
                & (
                    ExistsCriterion(
                        frappe.qb.from_(bom)
                        .select(bom.name)
                        .where((bom.item == pi.item_code) & (bom.is_active == 1))
                    )
                )
            )
        )

        if self.item_code:
            packed_items_query = packed_items_query.where(so_item.item_code == self.item_code)

        packed_items = packed_items_query.run(as_dict=True)

        self.add_items(items + packed_items)
        self.calculate_total_planned_qty()

class CustomWorkOrder(WorkOrder):
    def create_job_card(self):
        if not frappe.db.get_single_value("Manufacturing Settings", 'disable_auto_create_job_card'):
            super(CustomWorkOrder,self).create_job_card()

bom_map = {}

def get_sub_assembly_bom_map(company):
    if not bom_map:
        sql = """select bom.name, bom.item, bi.item_code, bi.item_name,bi.description, bi.stock_uom, item.is_sub_contracted_item,
                    def.default_warehouse,bi.bom_no, ifnull(sum(bi.stock_qty/ifnull(bom.quantity,1)),0) qty,
                    item.default_material_request_type
                from tabBOM bom inner join `tabBOM Item` bi on bom.name=bi.parent
                inner join tabItem item on item.name=bi.item_code
                left join `tabItem Default` def on def.parent=item.name and def.company=%s and def.parenttype='Item'
                where bom.company=%s and bom.is_active=1 and bom.docstatus=1 and bi.bom_no is not null and bi.bom_no <> ''
                group by bom.name,bom.item,bi.item_code,bi.bom_no,bi.item_name,bi.description, bi.stock_uom, 
                    item.is_sub_contracted_item,def.default_warehouse
                """               
        data = frappe.db.sql(sql, (company, company,), as_dict = True)
        for d in data:
            bom_items = bom_map.setdefault(d.name, [])
            bom_items.append(d)

def get_sub_assembly_qty_map(company):
    sql = """select bin.item_code, bin.warehouse, bin.projected_qty
            from tabBin bin inner join tabBOM bom on bin.item_code=bom.item
            where bom.company=%s and bom.is_active=1 and bom.docstatus=1
            """               
    data = frappe.db.sql(sql, (company,), as_dict = True)
    return {(d.item_code,d.warehouse):d.projected_qty for d in data}

def custom_get_sub_assembly_items(bom_no, bom_data, to_produce_qty, company, warehouse=None, indent=0, qty_map={}):
    data = bom_map.get(bom_no) or []
    for d in data:        
        stock_qty = d.qty * flt(to_produce_qty)
        required_qty = stock_qty        
        assigned_qty = 0
        row_warehouse = d.default_warehouse or warehouse # warehouse参数仅用于带入是否考虑半成品库存勾选参数
        if warehouse:#考虑半成品库存            
            key = (d.item_code, row_warehouse)            
            projected_qty = qty_map.get(key)
            if projected_qty:
                if projected_qty < 0:
                    assigned_qty = projected_qty  #实际上是将其它本范围外的需求加进来
                elif projected_qty >= stock_qty:
                    assigned_qty = stock_qty
                else:
                    assigned_qty = projected_qty
                qty_map[key] -= assigned_qty
                stock_qty -= assigned_qty
            if assigned_qty:
                msg = _("Per projected qty {0} planned qty by {1}").format(
                    _('Increated' if assigned_qty<0 else 'Decreased'), abs(assigned_qty))
        bom_data.append(
            frappe._dict(
                {
                    "parent_item_code": d.item,
                    "description": "\r\n".join([d.description, msg]) if assigned_qty else d.description,
                    "production_item": d.item_code,
                    "item_name": d.item_name,
                    "stock_uom": d.stock_uom,
                    "uom": d.stock_uom,
                    "bom_no": d.bom_no,
                    "is_sub_contracted_item": d.is_sub_contracted_item,
                    "default_material_request_type": d.default_material_request_type,
                    "bom_level": indent,
                    "indent": indent,
                    "warehouse": row_warehouse,
                    "required_qty": required_qty,
                    "stock_qty": stock_qty
                }
            )
        )

        if stock_qty:
            custom_get_sub_assembly_items(d.bom_no, bom_data, stock_qty, company, warehouse, indent=indent + 1, qty_map=qty_map)